
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class NewsManager {

    private final static String SQL_GET = "SELECT message,datecreation from news order by datecreation desc limit 10";

    

    private static ArrayList<News> getNews(Connection con) throws SQLException{
        ArrayList<News> list = new ArrayList<News>();
        Statement stat = con.createStatement();
        ResultSet rs = stat.executeQuery(SQL_GET);
        while (rs.next()){
            News n = new News();
            n.message = rs.getString("message");
            n.creationdate = rs.getTimestamp("datecreation");
            list.add(n);
        }
        rs.close();
        stat.close();
        return list;
    }
    
    private static String getXML(Document doc) throws TransformerConfigurationException, TransformerException {
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer transformer = tf.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        StringWriter writer = new StringWriter();
        transformer.transform(new DOMSource(doc), new StreamResult(writer));
        return writer.getBuffer().toString().replaceAll("\n|\r", "");
    }
    public static String getXMLNews(Connection con) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<News> list = getNews(con);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (News n : list){
            Node news = doc.createElement("news");
            news.setTextContent(n.message);
            Node datetime = doc.createAttribute("datetime");
            datetime.setNodeValue(sdf.format(n.creationdate));
            news.getAttributes().setNamedItem(datetime);
            root.appendChild(news);
        }
        return getXML(doc);
    }
    private static Document getBaseDocument() throws ParserConfigurationException {
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
        doc.appendChild(doc.createElement("crombz"));
        return doc;
    }
    public static Connection getConnection(String host, String db, String user, String pass) throws SQLException {
        String url = "jdbc:mysql://" + host + "/" + db;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(CrombManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return DriverManager.getConnection(url, user, pass);
    }

}
